package cn.itcast.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.utils.MyJdbcUtil;
import cn.itcast.vo.Customer;
import cn.itcast.vo.PageBean;
import lombok.Cleanup;

public class StatementCustomerDaoImpl implements CustomerDao {

	@Override
	public int save(Customer c) {
	    StringBuffer sql = new StringBuffer("insert into t_customer values (");
	    
	    sql.append("'" + c.getId() + "',");
	    sql.append("'" + c.getUsername() + "',");
	    sql.append("'" + c.getGender() + "',");
	    sql.append("'" + c.getBirthday() + "',");
	    sql.append("'" + c.getCellphone() + "',");
	    sql.append("'" + c.getEmail() + "',");
	    sql.append("'" + c.getLove() + "',");
	    sql.append("'" + c.getType() + "')");

	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup Statement s = conn.createStatement();
	    	return s.executeUpdate(sql.toString());
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public List<Customer> findAll() {
		String sql = "select * from t_customer";
	    List<Customer> list = new ArrayList<Customer>();
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup  Statement s = conn.createStatement();
	    	@Cleanup ResultSet rs = s.executeQuery(sql);
	    	
	    	while (rs.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	list.add(c);
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return list;
	}

	@Override
	public int delete(String id) {
		String sql = "delete from t_customer where id = '" + id + "'";
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup Statement s = conn.createStatement();
	    	
	    	return s.executeUpdate(sql);
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public Customer findById(String id) {
		String sql = "select * from t_customer where id = '" + id + "'";

	    Customer c = null;
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup Statement s = conn.createStatement();
	    	
	    	@Cleanup ResultSet rs = s.executeQuery(sql);
	    	
	    	while (rs.next()) {
	    		c = new Customer();
	    		
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	break;
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }

	    return c;
	}

	@Override
	public int update(Customer c) {
	    StringBuffer sql = new StringBuffer("update t_customer ");
	    
	    sql.append("set username = '" + c.getUsername() + "',");
	    sql.append("gender = '" + c.getGender() + "',");
	    sql.append("birthday = '" + c.getBirthday() + "',");
	    sql.append("cellphone = '" + c.getCellphone() + "',");
	    sql.append("email = '" + c.getEmail() + "',");
	    sql.append("love = '" + c.getLove() + "',");
	    sql.append("type = '" + c.getType() + "'");
	    sql.append("where id = '" + c.getId() + "'");
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup Statement s = conn.createStatement();
	    	
	    	return s.executeUpdate(sql.toString());
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public List<Customer> findByWhere(String username, String type) {

		StringBuffer sb = new StringBuffer("select * from t_customer where 1=1 ");
		
		if ((username != null) && (!username.trim().isEmpty()))
	    {
	      sb.append(" and username = '" + username + "' ");
	    }
		
	    if ((type != null) && (!type.trim().isEmpty()))
	    {
	      sb.append(" and type='" + type + "' ");
	    }
	    
	    List<Customer> list = new ArrayList<Customer>();
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup Statement s = conn.createStatement();
	    	
	    	@Cleanup ResultSet rs = s.executeQuery(sb.toString());
	    	
	    	while (rs.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	list.add(c);
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return list;
	}

	@Override
	public PageBean<Customer> findByPage(int pageCode, int pageSize) {
		PageBean<Customer> page = new PageBean<Customer>();
	    page.setPageCode(pageCode);
	    page.setPageSize(pageSize);

	    List<Customer> beanList = new ArrayList<Customer>();
	    
	    String countSql = "select count(*) from t_customer";
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	int count = -1;
	    	@Cleanup Statement s1 = conn.createStatement();
	    	if (s1.execute(countSql)) {
	    		@Cleanup ResultSet rs1 = s1.getResultSet();
	    		
		    	while (rs1.next()) {
		    		count = (int) rs1.getLong(1);
		    		break;
		    	}
		    	
		    	page.setTotalCount(count);
	    	}
	    	
	    	String selSql = "";
		    int m, n;
			
			if (MyJdbcUtil.isSqlServer) {
				selSql = "select top %d * from t_customer where id not in (select top %d id from t_customer)";
				m = pageSize;
				n = Integer.valueOf((pageCode - 1) * pageSize);
			} else if (MyJdbcUtil.isOracleServer) {
				selSql = "SELECT * FROM " +
				"(" +
				"SELECT A.*, ROWNUM RN " +  
				"FROM (SELECT * FROM t_customer) A " +
				"WHERE ROWNUM <= %d" +
				")" +
				"WHERE RN > %d";
				
				n = Integer.valueOf((pageCode - 1) * pageSize);
				m = n + pageSize;
			}  else if (MyJdbcUtil.isPostgresServer) {
				selSql = "select * from t_customer limit %d offset %d";
				n = Integer.valueOf((pageCode - 1) * pageSize);
				m = Integer.valueOf(pageSize);
			} else if (MyJdbcUtil.isInformixServer) {
				selSql = "SELECT SKIP %d FIRST %d * FROM t_customer";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
			} else {
				selSql = "select * from t_customer limit %d, %d";
				
				 m = Integer.valueOf((pageCode - 1) * pageSize);
				 n = Integer.valueOf(pageSize);
			}
			
			
			@Cleanup Statement s2 = conn.createStatement();
			@Cleanup ResultSet rs2 = s2.executeQuery(String.format(selSql, m, n));
			
	    	while (rs2.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs2.getString(1));
		    	c.setUsername(rs2.getString(2));
		    	c.setGender(rs2.getString(3));
		    	c.setBirthday(rs2.getString(4));
		    	c.setCellphone(rs2.getString(5));
		    	c.setEmail(rs2.getString(6));
		    	c.setLove(rs2.getString(7));
		    	c.setType(rs2.getString(8));
		    	
		    	beanList.add(c);
	    	}
	    	
	    	page.setBeanList(beanList);
	    } catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return page;
	    
	}

}
